CREATE DATABASE ERP
USE ERP
----------------------------------------------apchck----------------------------------------------
CREATE TABLE apchck (
	[cvendno] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[cpayto] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[cchktype] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[cbankno] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[cchkno] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ctogl] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[dcheck] [datetime] NOT NULL ,
	[dcreate] [datetime] NOT NULL ,
	[lcancel] [smallint] NOT NULL ,
	[lhold] [smallint] NOT NULL ,
	[nchkamt] [numeric](16, 2) NOT NULL ,
	[nfchkamt] [numeric](16, 2) NOT NULL ,
	[nxchgrate] [numeric](16, 6) NOT NULL ,
	[nbchkamt] [numeric](16, 2) NOT NULL 
)
GO
----------------------------------------------Trigger Cheques----------------------------------------------
CREATE TRIGGER TrigCheques ON ERP.dbo.apchck 
AFTER INSERT,UPDATE
AS
BEGIN
	INSERT INTO ReportesGerenciales.dbo.Cheques(no_vendedor,beneficiario,tipo_cheque,cuenta_banco,numero_cheque,incluido_cierre_contable,
	fecha_cheque,fecha_creacion_cheque,emitido_conciliacion_bancaria,retenido,cantidad,cheque_entregado)
	SELECT cvendno,cpayto,cchktype,cbankno,cchkno,ctogl,dcheck,dcreate,lcancel,lhold,nchkamt,0 FROM INSERTED;
END
----------------------------------------------HRJobs----------------------------------------------
CREATE TABLE HRJobs (
	cJobTitlNO 	VARCHAR (10)  NOT NULL ,
	cDesc		VARCHAR (40)  NOT NULL ,
	nSalary		DECIMAL(5, 2) NOT NULL ,
	nPorPlus	DECIMAL(5, 2) NOT NULL ,
	mnotepad	VARCHAR(250)  NULL ,
	tmodrec		datetime NOT NULL 
)
GO
----------------------------------------------Trigger Cargo----------------------------------------------
CREATE TRIGGER TrigCargo ON ERP.dbo.HRJobs 
AFTER INSERT,UPDATE
AS
BEGIN
	INSERT INTO ReportesGerenciales.dbo.Cargo(id_cargo,descripcion)
	SELECT cJobTitlNO,cDesc FROM INSERTED;
END
----------------------------------------------prdept----------------------------------------------
CREATE TABLE prdept (
	cdeptno char (10)  NOT NULL ,
	cdeptname char (30)  NOT NULL ,
	cwageacc char (30)  NOT NULL ,
	ctaxacc char (30)  NOT NULL ,
	cpaytype char (2)  NOT NULL ,
	cpayperiod char (2)  NOT NULL ,
	lallowot smallint NULL ,
	lepayment smallint NULL ,
	lusetips smallint NULL ,
	nrhr numeric(8, 2) NOT NULL ,
	cficaeracc char (30)  NOT NULL ,
	cmedieracc char (30)  NOT NULL ,
	cfutaeracc char (30)  NOT NULL ,
	csutaeracc char (30)  NOT NULL ,
	cficaacc char (30)  NOT NULL ,
	cmediacc char (30)  NOT NULL ,
	cfutaacc char (30)  NOT NULL ,
	csutaacc char (30)  NOT NULL ,
	cdeptglseg char (30)  NOT NULL 
)
GO
----------------------------------------------TRIGGER Departamento----------------------------------------------
CREATE TRIGGER TrigDepartamento ON ERP.dbo.prdept
AFTER INSERT,UPDATE
AS
BEGIN
	INSERT INTO ReportesGerenciales.dbo.Departamento(id_departamento,nombre)
	SELECT cdeptno,cdeptname FROM INSERTED;
END
----------------------------------------------TRIGGER Departamento----------------------------------------------
CREATE TABLE prempy (
	cempno char (10)  NOT NULL ,
	cfname char (20)  NOT NULL ,
	clname char (20)  NOT NULL ,
	cinitial char (2)  NOT NULL ,
	cssn char (15)  NULL ,
	cdeptno char (10)  NOT NULL ,
	cstatus char (1)  NOT NULL ,
	cfedid char (15)  NULL ,
	caddr1 char (40)  NOT NULL ,
	caddr2 char (40)  NOT NULL ,
	ccity char (20)  NOT NULL ,
	cstate char (2)  NOT NULL ,
	czip char (10)  NOT NULL ,
	cphone char (20)  NOT NULL ,
	cjobtitle char (10)  NOT NULL ,
	csex char (1)  NOT NULL ,
	ccitizen char (1)  NOT NULL ,
	ceeoid char (1)  NOT NULL ,
	cethniccd char (1)  NOT NULL ,
	cpaytype char (2)  NOT NULL ,
	cpayperiod char (2)  NOT NULL ,
	cmarry char (1)  NOT NULL ,
	cmtype char (1)  NOT NULL ,
	ceicstatus char (1)  NOT NULL ,
	ctaxstate char (2)  NOT NULL ,
	csutastate char (2)  NOT NULL ,
	cltaxstate char (2)  NOT NULL ,
	cltaxcd char (3)  NOT NULL ,
	capplycd char (10)  NOT NULL ,
	cprenote char (1)  NOT NULL ,
	cw2year char (4)  NOT NULL ,
	dhire datetime NULL ,
	dterminate datetime NULL ,
	dbirth datetime NULL ,
	deff datetime NULL ,
	dnextpf datetime NULL ,
	dnextsal datetime NULL ,
	dlastpaid datetime NULL ,
	dprenote datetime NULL ,
	lallowot smallint NULL ,
	lepayment smallint NULL ,
	lusetips smallint NULL ,
	lfica smallint NULL ,
	lmedi smallint NULL ,
	lfuta smallint NULL ,
	lsuta smallint NULL ,
	lsdi smallint NULL ,
	leic smallint NULL ,
	lftax smallint NULL ,
	lstax smallint NULL ,
	lsres smallint NULL ,
	lltax smallint NULL ,
	llres smallint NULL ,
	lapply smallint NULL ,
	nbpayrate numeric(16, 4) NOT NULL ,
	napayrate numeric(16, 4) NOT NULL ,
	nrhr numeric(8, 2) NOT NULL ,
	nafwt numeric(16, 2) NOT NULL ,
	nfallow numeric(2, 0) NOT NULL ,
	naswt numeric(16, 2) NOT NULL ,
	nsallow numeric(2, 0) NOT NULL ,
	nalwt numeric(16, 2) NOT NULL ,
	csrcid char (10)  NOT NULL ,
	cplnid char (10)  NOT NULL ,
	cshfid char (10)  NOT NULL ,
	cmsc1id char (10)  NOT NULL ,
	cmsc2id char (10)  NOT NULL ,
	cmsc3id char (10)  NOT NULL ,
	cmsc4id char (10)  NOT NULL ,
	cmsc5id char (10)  NOT NULL ,
	csegid1 char (10)  NOT NULL ,
	csegid2 char (10)  NOT NULL ,
	csegid3 char (10)  NOT NULL ,
	csegid4 char (10)  NOT NULL ,
	csegid5 char (10)  NOT NULL ,
	lPaySlip smallint NOT NULL ,
	ccustno char (10)  NOT NULL ,
	cdayoff char (1)  NOT NULL ,
	cbatchid char (10)  NOT NULL ,
	nmonthpay numeric(16, 2) NOT NULL ,
	nlstmthpay numeric(16, 2) NOT NULL ,
	ltemporal smallint NOT NULL ,
	nfixover numeric(16, 2) NOT NULL ,
	crtn char (15)  NOT NULL ,
	dcntrct datetime NULL ,
	laddtax1 smallint NOT NULL ,
	laddtax2 smallint NOT NULL ,
	laddtax3 smallint NOT NULL ,
	ltax11mth smallint NOT NULL 
)
GO
----------------------------------------------TRIGGER Empleado----------------------------------------------
CREATE TRIGGER TrigEmpleado ON ERP.dbo.prempy
AFTER INSERT,UPDATE
AS
BEGIN
	INSERT INTO ReportesGerenciales.dbo.Empleado(codigo_empleado,identidad,nombres,apellidos,codigo_departamento,
	sueldo,fecha_contrato,acuerdo,fecha_nacimiento,impuesto_estatal,tipo_planilla,estado,sexo,id_cargo)
	SELECT cempno,cfedid,cfname,clname,cdeptno,nmonthpay,dcntrct,dhire,dbirth,ctaxstate,cpaytype,cstate,csex,cjobtitle
	FROM INSERTED;
END
----------------------------------------------prempg----------------------------------------------
CREATE TABLE [prempg] (
	[cuid] [char] (15) DEFAULT (''),
	[cempno] [char] (10) DEFAULT (''),
	[cwageacc] [char] (30) DEFAULT (''),
	[ctaxacc] [char] (30) DEFAULT (''),
	[ndistpct] [numeric](6, 2) NOT NULL DEFAULT (0),
	[cfwtacc] [char] (30)  DEFAULT (''),
	[cficaacc] [char] (30)  DEFAULT (''),
	[cmediacc] [char] (30)  DEFAULT (''),
	[cfutaacc] [char] (30) DEFAULT (''),
	[csutaacc] [char] (30) DEFAULT (''),
	[cficaeracc] [char] (30) DEFAULT (''),
	[cmedieracc] [char] (30) DEFAULT (''),
	[cfutaeracc] [char] (30) DEFAULT (''),
	[csutaeracc] [char] (30) DEFAULT (''),
	PRIMARY KEY  CLUSTERED ([cuid])
)
GO
----------------------------------------------TRIGGER Cuentas_Empleado----------------------------------------------
CREATE TRIGGER TrigCuentasEmpleado ON ERP.dbo.prempg
AFTER INSERT,UPDATE
AS
BEGIN
	INSERT INTO ReportesGerenciales.dbo.Cuentas_Empleado(codigo_empleado,codigo_presupuestario)
	SELECT cempno,cwageacc FROM INSERTED;
END
----------------------------------------------prmisc----------------------------------------------
CREATE TABLE prmisc (
	[cuid] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[cempno] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[clname] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[cfname] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ctaxstate] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[cdeptno] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[cpaytype] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[cref] [char] (54) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ctrsno] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[cbankno] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[cchkno] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[dtrs] [datetime] NULL ,
	[dcheck] [datetime] NULL ,
	[ltaxable] [smallint] NULL ,
	[nothtax] [numeric](16, 2) NULL ,
	[nothntax] [numeric](16, 2) NULL ,
	[csegid1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[csegid2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[csegid3] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[csegid4] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[csegid5] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[csegmid] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ccycle] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[cpayno] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[cpaycode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[nqty] [numeric](16, 4) NULL ,
	[nrate] [numeric](16, 4) NULL ,
	[lsystem] [smallint] NULL ,
	[nothertax] [numeric](16, 2) NULL ,
	[nBaseGrTax] [numeric](16, 2) NULL ,
	[cdocno] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[cdoctyp] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
)
GO
----------------------------------------------TRIGGER IngresoXEmpleado----------------------------------------------
CREATE TRIGGER TrigIngresoXEmpleado ON ERP.dbo.prmisc
AFTER INSERT,UPDATE
AS
BEGIN
	INSERT INTO ReportesGerenciales.dbo.IngresoXEmpleado(codigo_empleado,referencia,banco,cheque,planilla,
	codigo_ingreso,cantidad,tasa)
	SELECT cempno,cref,cbankno,cchkno,cpayno,cpaycode,nqty,nrate FROM INSERTED;
END
----------------------------------------------Prmisd----------------------------------------------
CREATE TABLE prmisd (
	[cuid] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[cempno] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[cdedno] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[cdedcode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[cpayno] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ndedamt] [numeric](16, 2) NOT NULL ,
	[tmodrec] [datetime] NOT NULL ,
	[lsystem] [smallint] NOT NULL ,
	[cdesc] [char] (54) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ctrsno] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
)
----------------------------------------------TRIGGER DeduccionXEmpleado----------------------------------------------
CREATE TRIGGER TrigDeduccionXEmpleado ON ERP.dbo.prmisd
AFTER INSERT,UPDATE
AS
BEGIN
	INSERT INTO ReportesGerenciales.dbo.DeduccionXEmpleado(codigo_empleado,planilla,codigo_ingreso_deduccion,
	descripcion,valor_deducciones)
	SELECT cempno,cpayno,cdedcode,cdesc,ndedamt FROM INSERTED;
END
----------------------------------------------bgbudt----------------------------------------------
CREATE TABLE bgbudt (
	[cuid] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[cbudid] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[cbudno] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[cdesc] [char] (54) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[cstmacct] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[crsvacct] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[cclsid] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ctypid] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[cdeptno] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[namt] [numeric](16, 2) NOT NULL ,
	[namtused] [numeric](16, 2) NOT NULL ,
	[nqty] [numeric](16, 4) NOT NULL ,
	[nqtyused] [numeric](16, 4) NOT NULL ,
	[tmodrec] [datetime] NOT NULL ,
	[namtaccr] [numeric](16, 2) NOT NULL ,
	[nqtyaccr] [numeric](16, 4) NOT NULL ,
	[namtmod] [numeric](16, 2) NOT NULL ,
	[nqtymod] [numeric](16, 4) NOT NULL 
)
----------------------------------------------TRIGGER PresupuestoXPeriodo----------------------------------------------
CREATE TRIGGER TrigPresupuestoXPeriodo ON ERP.dbo.bgbudt
AFTER INSERT,UPDATE
AS
BEGIN
	INSERT INTO ReportesGerenciales.dbo.PresupuestoXPeriodo(codigo,presupuesto,cantidad_modificada,cantidad,
	cantidad_usada)
	SELECT cbudno,cbudid,namtmod,namt,namtused FROM INSERTED;
END